from lxml import etree
import requests
from concurrent.futures import ThreadPoolExecutor
import 录入数据库方法

child_url_list = []
joyid_list = []
id_list = []
headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
                  "AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36"

}


def diqu():
    createTable()
    for i in range(0, len(child_url_list)):

        child_url = child_url_list[i]
        page = requests.get(url=child_url, headers=headers)
        code = page.status_code
        if code == 200:
            page = page.text
            tree = etree.HTML(page)

            joy = tree.xpath('/html/body/section/article/div/div[1]/div[2]/div[1]/div[1]/a[1]/text()')
            joy = joy[0] if joy else ""

            zhaopinrenshu = tree.xpath('//div[@class="job_content"]/ul[1]/li[1]/span[2]/text()')
            zhaopinrenshu = zhaopinrenshu[0] if zhaopinrenshu else ""

            shangbandidian = tree.xpath('//div[@class="job_content"]/ul[1]/li[2]/span[2]/text()')
            shangbandidian = shangbandidian[0] if shangbandidian else ""

            shijianyaoqiu = tree.xpath('//div[@class="job_content"]/ul[2]/li[1]/span[2]/text()')  # []
            shijianyaoqiu = shijianyaoqiu[0] if shijianyaoqiu else ""

            gongzuozhonglie = tree.xpath('//div[@class="job_content"]/ul[2]/li[2]/span[2]/text()')
            gongzuozhonglie = gongzuozhonglie[0] if gongzuozhonglie else ""

            meizhouzhishaogongzuoshijian = tree.xpath('//div[@class="job_content"]/ul[2]/li[3]/span[2]/text()')
            meizhouzhishaogongzuoshijian = meizhouzhishaogongzuoshijian[0] if meizhouzhishaogongzuoshijian else ""

            shangbanshiduan = tree.xpath('//div[@class="job_content"]/ul[2]/li[4]/span[2]/text()')
            shangbanshiduan = shangbanshiduan[0] if shangbanshiduan else ""

            jiesuanfangshi = tree.xpath('//div[@class="job_content"]/ul[3]/li[1]/span[2]/text()')
            jiesuanfangshi = jiesuanfangshi[0] if jiesuanfangshi else ""

            gongzi = tree.xpath('//div[@class="job_content"]/ul[3]/li[2]/span[2]/text()')
            gongzi = gongzi[0] if gongzi else ""

            list_text = [a.strip() for a in tree.xpath('//div[@class="box"]//text()')]
            gongzuoxiangqing = ""
            for a in range(0, len(list_text)):
                if list_text[a] != '':
                    gongzuoxiangqing = gongzuoxiangqing + list_text[a]
                else:
                    gongzuoxiangqing = gongzuoxiangqing
            gongsimingzi = tree.xpath('//div[@class ="company_info"]/a/text() ')
            gongsimingzi = gongsimingzi[0] if gongsimingzi else ""

            gongshijieshao = tree.xpath('//div[@class ="company_info"]/p[1]/text() ')
            gongshijieshao = gongshijieshao[0] if gongshijieshao else ""

            gongshidizhi = tree.xpath('//div[@class ="company_info"]/p[2]/text()')
            gongshidizhi = gongshidizhi[0] if gongshidizhi else ""
            id = id_list[i]
            joyid = joyid_list[i]
            url = child_url
            addJoyData(id, joyid, url, gongzi, joy, zhaopinrenshu, shangbandidian,
                       shijianyaoqiu, gongzuozhonglie, meizhouzhishaogongzuoshijian, shangbanshiduan, jiesuanfangshi,
                       gongzuoxiangqing, gongsimingzi, gongshijieshao, gongshidizhi)
            addzhuangtaima(id)
        else:
            print('404 Not Found')
def addzhuangtaima(id):
    sql = '''update 兼职猫城市下地区 set 状态码=1 where id="%s" '''%(id)
    录入数据库方法.modifyTable(sql)

def getyemianxingxi():
    '''
    获取数据库url,id
    :return:
    '''
    sql = 'select child_url,joyid,id from 兼职猫城市下地区 where 状态码=0'
    result = 录入数据库方法.query(sql)
    for el in result:
        child_url = el[0]
        child_url_list.append(child_url + "")
        joyid = el[1]
        joyid_list.append(joyid)
        id = el[2]
        id_list.append(id)
    return child_url_list, joyid_list, id_list,


def createTable():
    '''
    建表
    :return:
    '''
    sql = '''
    create table if not exists 兼职猫工作详情 (
  `id` varchar(50) NOT NULL,
  `joyid` varchar(50) NOT NULL,
  `child_url` varchar(100) DEFAULT NULL,
  `gongzi` varchar(20) DEFAULT NULL,
  `joy` varchar(15) DEFAULT NULL,
  `zhaopinrenshu` varchar(10) DEFAULT NULL,
  `shangbandidian` text,
  `shijianyaoqiu` text,
  `gongzuozhonglie` text,
  `meizhouzhishaogongzuoshijian` varchar(10) DEFAULT NULL,
  `shangbanshiduan` varchar(10) DEFAULT NULL,
  `jiesuanfangshi` text,
  `gongzuoxiangqing` text,
  `gongsimingzi` text,
  `gongshijieshao` text,
  `gongshidizhi` text,
  PRIMARY KEY (`joyid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    '''
    录入数据库方法.createTable(sql=sql)


def addJoyData(id, joyid, url, gongzi, joy, zhaopinrenshu, shangbandidian,
               shijianyaoqiu, gongzuozhonglie, meizhouzhishaogongzuoshijian, shangbanshiduan, jiesuanfangshi,
               gongzuoxiangqing, gongsimingzi, gongshijieshao, gongshidizhi):
    sql = '''insert into 兼职猫工作详情(id,joyid,child_url,gongzi,joy,zhaopinrenshu,shangbandidian,
        shijianyaoqiu,gongzuozhonglie,meizhouzhishaogongzuoshijian,shangbanshiduan,jiesuanfangshi,
        gongzuoxiangqing,gongsimingzi,gongshijieshao,gongshidizhi)
        values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')
        ''' % (id, joyid, url, gongzi, joy, zhaopinrenshu, shangbandidian,
               shijianyaoqiu, gongzuozhonglie, meizhouzhishaogongzuoshijian, shangbanshiduan, jiesuanfangshi,
               gongzuoxiangqing, gongsimingzi, gongshijieshao, gongshidizhi)
    录入数据库方法.modifyTable(sql)


if __name__ == '__main__':
    getyemianxingxi()
    createTable()
    with ThreadPoolExecutor(10) as th:
        for i in range(1,len(child_url_list)):
            th.submit(diqu())
